Chapter 20: Pandas
From book
Python Programming (Problem solving, Packages and Libraries)
Published by McGraw Hill Education (India) Private limited.
By:
- Anurag Gupta
- G. P. Biswas
This is Part 2 of the html document on Chapter 20 Pandas
This Assignment/ Project is given on page2 537-546 of the book
Exercise:- Use of pandas library¶
What you should know:-¶
- You should know how to download and unzip data from net.
- Basics of python language
- Basics of pandas library
- Basics of matplotlib.
- You should have anaconda distribution and Jupyter notebook installed to follow along the exercise.
Data set used:-¶
- The description of data used for this exercise is available at:- http://archive.ics.uci.edu/ml/datasets/Drug+Review+Dataset+%28Drugs.com%29
- Download data from:- http://archive.ics.uci.edu/ml/machine-learning-databases/00462/drugsCom_raw.zip
- Guidelines for use and restrictions on how data may be used are given on the web site. Please follow those guidelines while using the data and if you use the data for any research publication, please remember to cite the data source .
Details of the data:-¶
- When you unzip the zipped file , you will get 2 files namely:-
- drugsComTrain_raw.tsv
- drugsComTest_raw.tsv
- File extension .tsv stands for tab seperated values.
- We will use the file drugsComTrain_raw.tsv for this exercise.
Columns in the data (ie attributes of data):-¶
(Note:- Categorical means that the data is a category or name of and so not numeric.)
- drugName (categorical): name of drug
- condition (categorical): name of condition
- review (text): patient review
- rating (numerical): 10 star patient rating
- date (date): date of review entry
- usefulCount (numerical): number of users who found review useful
Learning steps and objectives:-¶
- Use the read_csv() method of pandas to create a DataFrame object. Since this is a tsv file use sep = '\t'. ( '\t' stands for tab and sep is the seperator attribute)
- Use shape attribute of DataFrame object to know number of rows (ie attributes) and columns in the DataFrame object.
- Learn to rename a column in the DataFrame object.
- Learn to use the head(n) method of the DataFrame object to get n number of rows.
- Learn to use T to get Transpose of the DataFrame ie to get columns as rows and rows as columns. (This is useful to show the columns in one place in the printout, because if you have large number of columns in the DataFrame then they will be split in the printout).
- Learn to select items from a particular column of the DataFrame object.
- Use value_counts() method on a particular column of the DataFrame object to get count of unique items in a column.
- Use the describe() method to get summary of the data in the DataFrame.
- Learn to find out null values ie NaN (Not a Number) in the data.
- Remove those 'rows' ie 'index' whose values are NaN for a particular column. In the present data set, there are some NaNs in the 'condition' column, so they are removed. (Note there can be different ways of dealing with NaNs. You could convert them to some number say 0 or some string or some other object also).
- Learn to 'change' the type of object held in a particular column. This data set has a column 'date'. But the 'type' of this column is 'object'. So we will convert the 'object' type to 'date' type.
- Our objective is to plot the number of 'rating' per year. So what we have to do is to take the 'date' column and take its year part and group on the year part of the date and then count the number of entries for each year.
- To do the above task we need to learn to use the groupby() method and agg() method to get the total entries for a particular year. Note that in the groupby() method, we will not groupby 'date', but rather we will group by the year part of the date column. To get the year part of the date column, we need to use my_df.date.dt.year ie the df_object.column_name.dt.year because here the DataFrame object is my_df, the column name is 'date'.
- Finally we need to use the matplotlib library to plot year on x-axis and the review count on the y-axis.
In [1]:
# Load .tsv file in a DataFrame object and see its shape
import pandas as pd
path2file = r'C:\temp_data\drugsComTrain_raw.tsv' # Use your file path instead
# Read the data into a pandas DataFrame object
my_df = pd.read_csv(path2file, sep = '\t', error_bad_lines=False)
# Get shape of data as a tuple
print(my_df.shape)
#To get rows
print('rows->', my_df.shape[0])
# To get columns
print('columns->', my_df.shape[1])
# To get names of all the columns
print(my_df.columns)
In [2]:
# Lets rename the first column which is unnamed
my_df.columns.values[0] = 'someNumber'
In [3]:
# Get 1 row along with column names but as a transpose (Use T)
# When you use T, the column names are shown as rows
print(my_df.head(1).T)
In [4]:
# Lets get the index of the data ie number of rows in the DataSet object
print(my_df.index)
In [5]:
# Lets get the data types for each column
print(my_df.dtypes)
In [6]:
# Suppose you want only first 5 drug names
print(my_df['drugName'].head(5))
In [7]:
# How many unique drugs are there?
print(my_df.drugName.nunique())
In [8]:
# Which are the 5 most common drugs?
my_df.drugName.value_counts().head(5) # value_counts() note pulural in value_counts()
Out[8]:
You can see the complete signature of the describe() method at:- http://pandas.pydata.org/pandas-docs/version/0.17/generated/pandas.DataFrame.describe.html
In [9]:
# Describe the drugs data
# Default is to provide a summary for the numerical columns only.
# include = 'all' gives summary of all the columns
my_df.describe(include = 'all')
Out[9]:
In [10]:
# But by default describe() gives summary of numeric columns only
my_df.describe()
Out[10]:
In [11]:
# include='object' gives summary of character columns
my_df.describe(include=['object'])
Out[11]:
In [12]:
# If you want to get summary of a particular column say drugName
my_df.drugName.describe()
Out[12]:
In [13]:
# Get oldest and latest dates
oldest_date = min(my_df['date'])
print('oldest date->', oldest_date)
latest_date = max(my_df['date'])
print('latest date->', latest_date)
Cleaning the data¶
In [14]:
# Count number of NaN or null
my_df.isnull().sum()
Out[14]:
In [15]:
# Clean the data
# drop NaNs in the 'condition' column and update the dataframe
my_df.dropna(subset = ['condition'], inplace = True)
# Check that the NaN are removed
my_df.isnull().sum()
Out[15]:
In [16]:
# We want to see how many reviews were there in each year.
# The date column is of data type 'object' and has day, month and year.
# We only want the year part
# First change date column from object to datetime format
my_df['date'] = pd.to_datetime(arg = my_df['date'])
# Pick up only the year part of the date
# df_drugs is a new DataFrame object created from my_df
df_drugs = pd.DataFrame(my_df['date'].groupby(my_df.date.dt.year).agg('count'))
# Rename the column of new dataFrame object to 'Count'
df_drugs.columns = ['Count']
# Rename index of new DataFrame object ie df_drugs to 'Year'
df_drugs.index.names = ['Year']
# Check that we got total review count for each year
print(df_drugs)
In [17]:
import matplotlib.pyplot as plt
plt.style.use(['seaborn'])
ax = df_drugs.plot(kind = 'bar')
x_labels = df_drugs.index
ax.set_xticklabels(x_labels)
# Get name of index and set x-label to name of index
xlabel = df_drugs.index.name
ax.set_xlabel(xlabel)
ax.set_ylabel('Review_Count')
ax.set_title('Reviews per Year')
plt.show()
The entire code is given below in one place.¶
(If you dont have or are not using Jupyter notebook, you may copy and paste the code below and it should run.)
Off course you must:-
- Have pandas and matplotlib libraries installed
- Download the data set and
- Give the path to the place where you have stored the downloaded files.
In [18]:
# The entire code in one place
import pandas as pd
path2file = r'C:\temp_data\drugsComTrain_raw.tsv' # Use your file path instead
# Read the data into a pandas DataFrame object
my_df = pd.read_csv(path2file, sep = '\t', error_bad_lines=False)
# Get shape of data as a tuple
print(my_df.shape)
#To get rows
print('rows->', my_df.shape[0])
# To get columns
print('columns->', my_df.shape[1])
# To get names of all the columns
print(my_df.columns)
# Lets rename the first column which is unnamed
my_df.columns.values[0] = 'someNumber'
# Get 1 row along with column names but as a transpose (Use T)
# When you use T, the column names are shown as rows
print(my_df.head(1).T)
# Lets get the index of the data ie number of rows in the DataSet object
print(my_df.index)
# Lets get the data types for each column
print(my_df.dtypes)
# Suppose you want only first 5 drug names
print(my_df['drugName'].head(5))
# How many unique drugs are there?
print(my_df.drugName.nunique())
# Which are the 5 most common drugs?
my_df.drugName.value_counts().head(5) # value_counts() note pulural in value_counts()
# Describe the drugs data
# Default is to provide a summary for the numerical columns only.
# include = 'all' gives summary of all the columns
my_df.describe(include = 'all')
# But by default describe() gives summary of numeric columns only
my_df.describe()
# include='object' gives summary of character columns
my_df.describe(include=['object'])
# If you want to get summary of a particular column say drugName
my_df.drugName.describe()
# Get oldest and latest dates
oldest_date = min(my_df['date'])
print('oldest date->', oldest_date)
latest_date = max(my_df['date'])
print('latest date->', latest_date)
# Count number of NaN or null
my_df.isnull().sum()
# Clean the data
# drop NaNs in the 'condition' column and update the dataframe
my_df.dropna(subset = ['condition'], inplace = True)
# Check that the NaN are removed
my_df.isnull().sum()
# We want to see how many reviews were there in each year.
# The date column is of data type 'object' and has day, month and year.
# We only want the year part
# First change date column from object to datetime format
my_df['date'] = pd.to_datetime(arg = my_df['date'])
# Pick up only the year part of the date
# df_drugs is a
df_drugs = pd.DataFrame(my_df['date'].groupby(my_df.date.dt.year).agg('count'))
# Rename the column of new dataFrame object to 'Count'
df_drugs.columns = ['Count']
# Rename index of DataFrame to 'Year'
df_drugs.index.names = ['Year']
# Check that we got total review count for each year
print(df_drugs)
import matplotlib.pyplot as plt
plt.style.use(['seaborn'])
ax = df_drugs.plot(kind = 'bar')
x_labels = df_drugs.index
ax.set_xticklabels(x_labels)
# Get name of index and set x-label to name of index
xlabel = df_drugs.index.name
ax.set_xlabel(xlabel)
ax.set_ylabel('Count')
ax.set_title('Reviews per Year')
plt.show()
